﻿CREATE TRIGGER tr_Technician_Tracker_TrackerID
ON dbo.t_Technician_Tracker 
FOR INSERT, UPDATE
AS

DECLARE @Technician sysname,@TrackerID varchar(50)

SELECT @Technician = t.FirstName+' '+t.LastName, @TrackerID = i.TrackerID
FROM inserted AS i
INNER JOIN t_Technician_Tracker tt
			ON i.TrackerID = tt.TrackerID AND
				 i.TechnicianID <> tt.TechnicianID
INNER JOIN t_Technician t ON tt.TechnicianID = t.ID

IF @TrackerID IS NULL RETURN

ROLLBACK TRAN
RAISERROR('Tracker %s is already assigned to %s.',16,1,@TrackerID,@Technician)
